{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Self join\n",
    "\n",
    "## Edinburgh Buses\n",
    "[Details of the database](https://sqlzoo.net/wiki/Edinburgh_Buses.) Looking at the data\n",
    "\n",
    "```\n",
    "stops(id, name)\n",
    "route(num, company, pos, stop)\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ····\n"
     ]
    }
   ],
   "source": [
    "import getpass\n",
    "import psycopg2\n",
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "pwd = getpass.getpass()\n",
    "engine = create_engine(\n",
    "    'postgresql+psycopg2://postgres:%s@localhost/sqlzoo' % (pwd))\n",
    "pd.set_option('display.max_rows', 100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "How many **stops** are in the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "stops = pd.read_sql_table('stops', engine)\n",
    "route = pd.read_sql_table('route', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <td>246</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      0\n",
       "id  246"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(stops[['id']].count())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "Find the **id** value for the stop 'Craiglockhart'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id\n",
       "52  53"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stops.loc[stops['name']=='Craiglockhart', ['id']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "Give the **id** and the **name** for the **stops** on the '4' 'LRT' service."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>19</td>\n",
       "      <td>Bingham</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263</th>\n",
       "      <td>53</td>\n",
       "      <td>Craiglockhart</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>397</th>\n",
       "      <td>85</td>\n",
       "      <td>Fairmilehead</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>541</th>\n",
       "      <td>115</td>\n",
       "      <td>Haymarket</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>566</th>\n",
       "      <td>117</td>\n",
       "      <td>Hillend</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>692</th>\n",
       "      <td>149</td>\n",
       "      <td>London Road</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>834</th>\n",
       "      <td>177</td>\n",
       "      <td>Northfield</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>846</th>\n",
       "      <td>179</td>\n",
       "      <td>Oxgangs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>928</th>\n",
       "      <td>194</td>\n",
       "      <td>Princes Street</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id            name\n",
       "45    19         Bingham\n",
       "263   53   Craiglockhart\n",
       "397   85    Fairmilehead\n",
       "541  115       Haymarket\n",
       "566  117         Hillend\n",
       "692  149     London Road\n",
       "834  177      Northfield\n",
       "846  179         Oxgangs\n",
       "928  194  Princes Street"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = stops.merge(route, how='left', left_on='id', right_on='stop')\n",
    "a.loc[(a['num']=='4') & (a['company']=='LRT'), ['id', 'name']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Routes and stops\n",
    "\n",
    "The query shown gives the number of routes that visit either London Road (149) or Craiglockhart (53). Run the query and notice the two services that link these stops have a count of 2. Add a HAVING clause to restrict the output to these two routes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>company</th>\n",
       "      <th>num</th>\n",
       "      <th>stop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>LRT</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>LRT</td>\n",
       "      <td>45</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   company num  stop\n",
       "7      LRT   4     2\n",
       "11     LRT  45     2"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(route.loc[route['stop'].isin((149, 53)),\n",
    "         ['company', 'num', 'stop']]\n",
    "    .groupby(['company', 'num'])\n",
    "    .count()\n",
    "    .reset_index()\n",
    "    .query('stop==2'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.\n",
    "Execute the self join shown and observe that b.stop gives all the places you can get to from Craiglockhart, without changing routes. Change the query so that it shows the services from Craiglockhart to London Road."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>company</th>\n",
       "      <th>num</th>\n",
       "      <th>stop_x</th>\n",
       "      <th>stop_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3018</th>\n",
       "      <td>LRT</td>\n",
       "      <td>4</td>\n",
       "      <td>53</td>\n",
       "      <td>149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8680</th>\n",
       "      <td>LRT</td>\n",
       "      <td>45</td>\n",
       "      <td>53</td>\n",
       "      <td>149</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     company num  stop_x  stop_y\n",
       "3018     LRT   4      53     149\n",
       "8680     LRT  45      53     149"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(route.merge(route, how='inner', \n",
    "             left_on=['company', 'num'], right_on=['company', 'num'])\n",
    "    .query('(stop_x==53) & (stop_y==149)')\n",
    "    .loc[:, ['company', 'num', 'stop_x', 'stop_y']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.\n",
    "The query shown is similar to the previous one, however by joining two copies of the **stops** table we can refer to **stops** by **name** rather than by number. Change the query so that the services between 'Craiglockhart' and 'London Road' are shown. If you are tired of these places try 'Fairmilehead' against 'Tollcross'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>company</th>\n",
       "      <th>num</th>\n",
       "      <th>name_x</th>\n",
       "      <th>name_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>361</th>\n",
       "      <td>LRT</td>\n",
       "      <td>4</td>\n",
       "      <td>Craiglockhart</td>\n",
       "      <td>London Road</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>362</th>\n",
       "      <td>LRT</td>\n",
       "      <td>45</td>\n",
       "      <td>Craiglockhart</td>\n",
       "      <td>London Road</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    company num         name_x       name_y\n",
       "361     LRT   4  Craiglockhart  London Road\n",
       "362     LRT  45  Craiglockhart  London Road"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(route.merge(route, how='inner', \n",
    "            left_on=['company', 'num'], right_on=['company', 'num'])\n",
    "    .merge(stops, left_on='stop_x', right_on='id')\n",
    "    .merge(stops, left_on='stop_y', right_on='id')\n",
    "    .query('(name_x==\"Craiglockhart\") & (name_y==\"London Road\")')\n",
    "    .loc[:, ['company', 'num', 'name_x', 'name_y']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. [Using a self join](https://sqlzoo.net/wiki/Using_a_self_join)\n",
    "\n",
    "Give a list of all the services which connect stops 115 and 137 ('Haymarket' and 'Leith')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>company</th>\n",
       "      <th>num</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6643</th>\n",
       "      <td>LRT</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6941</th>\n",
       "      <td>LRT</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7256</th>\n",
       "      <td>LRT</td>\n",
       "      <td>2A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10944</th>\n",
       "      <td>SMT</td>\n",
       "      <td>C5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11192</th>\n",
       "      <td>LRT</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11321</th>\n",
       "      <td>LRT</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      company num\n",
       "6643      LRT  12\n",
       "6941      LRT   2\n",
       "7256      LRT  2A\n",
       "10944     SMT  C5\n",
       "11192     LRT  22\n",
       "11321     LRT  25"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(route.merge(route, how='inner', \n",
    "            left_on=['company', 'num'], right_on=['company', 'num'])\n",
    "    .query('stop_x==115 & stop_y==137')\n",
    "    .loc[:, ['company', 'num']]\n",
    "    .drop_duplicates())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8.\n",
    "Give a list of the services which connect the stops 'Craiglockhart' and 'Tollcross'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>company</th>\n",
       "      <th>num</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8325</th>\n",
       "      <td>LRT</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8326</th>\n",
       "      <td>LRT</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8327</th>\n",
       "      <td>LRT</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8328</th>\n",
       "      <td>LRT</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     company num\n",
       "8325     LRT  47\n",
       "8326     LRT  45\n",
       "8327     LRT  27\n",
       "8328     LRT  10"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(route.merge(route, how='inner', \n",
    "            left_on=['company', 'num'], right_on=['company', 'num'])\n",
    "    .merge(stops, left_on='stop_x', right_on='id')\n",
    "    .merge(stops, left_on='stop_y', right_on='id')\n",
    "    .query('name_x==\"Craiglockhart\" & name_y==\"Tollcross\"')\n",
    "    .loc[:, ['company', 'num']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9.\n",
    "Give a distinct list of the **stops** which may be reached from 'Craiglockhart' by taking one bus, including 'Craiglockhart' itself, offered by the LRT company. Include the company and bus no. of the relevant services."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name_y</th>\n",
       "      <th>company</th>\n",
       "      <th>num</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>361</th>\n",
       "      <td>London Road</td>\n",
       "      <td>LRT</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>362</th>\n",
       "      <td>London Road</td>\n",
       "      <td>LRT</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1395</th>\n",
       "      <td>Hanover Street</td>\n",
       "      <td>LRT</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1396</th>\n",
       "      <td>Hanover Street</td>\n",
       "      <td>LRT</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1397</th>\n",
       "      <td>Hanover Street</td>\n",
       "      <td>LRT</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1812</th>\n",
       "      <td>Canonmills</td>\n",
       "      <td>LRT</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1813</th>\n",
       "      <td>Canonmills</td>\n",
       "      <td>LRT</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2074</th>\n",
       "      <td>Crewe Toll</td>\n",
       "      <td>LRT</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3641</th>\n",
       "      <td>Leith</td>\n",
       "      <td>LRT</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5130</th>\n",
       "      <td>Princes Street</td>\n",
       "      <td>LRT</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5131</th>\n",
       "      <td>Princes Street</td>\n",
       "      <td>LRT</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5517</th>\n",
       "      <td>Newhaven</td>\n",
       "      <td>LRT</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6358</th>\n",
       "      <td>Haymarket</td>\n",
       "      <td>LRT</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6835</th>\n",
       "      <td>Brunstane</td>\n",
       "      <td>LRT</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7331</th>\n",
       "      <td>Northfield</td>\n",
       "      <td>LRT</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7332</th>\n",
       "      <td>Northfield</td>\n",
       "      <td>LRT</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7424</th>\n",
       "      <td>Balerno</td>\n",
       "      <td>LRT</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7462</th>\n",
       "      <td>Balerno Church</td>\n",
       "      <td>LRT</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7515</th>\n",
       "      <td>Cockburn Crescent</td>\n",
       "      <td>LRT</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7585</th>\n",
       "      <td>Currie</td>\n",
       "      <td>LRT</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7586</th>\n",
       "      <td>Currie</td>\n",
       "      <td>LRT</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7850</th>\n",
       "      <td>Bingham</td>\n",
       "      <td>LRT</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7877</th>\n",
       "      <td>Craiglockhart</td>\n",
       "      <td>LRT</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7878</th>\n",
       "      <td>Craiglockhart</td>\n",
       "      <td>LRT</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7879</th>\n",
       "      <td>Craiglockhart</td>\n",
       "      <td>LRT</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7880</th>\n",
       "      <td>Craiglockhart</td>\n",
       "      <td>LRT</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7881</th>\n",
       "      <td>Craiglockhart</td>\n",
       "      <td>LRT</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7936</th>\n",
       "      <td>Fairmilehead</td>\n",
       "      <td>LRT</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7995</th>\n",
       "      <td>Hillend</td>\n",
       "      <td>LRT</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8030</th>\n",
       "      <td>Oxgangs</td>\n",
       "      <td>LRT</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8031</th>\n",
       "      <td>Oxgangs</td>\n",
       "      <td>LRT</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8140</th>\n",
       "      <td>Colinton</td>\n",
       "      <td>LRT</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8141</th>\n",
       "      <td>Colinton</td>\n",
       "      <td>LRT</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8142</th>\n",
       "      <td>Colinton</td>\n",
       "      <td>LRT</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8222</th>\n",
       "      <td>Duddingston</td>\n",
       "      <td>LRT</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8257</th>\n",
       "      <td>Riccarton Campus</td>\n",
       "      <td>LRT</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8325</th>\n",
       "      <td>Tollcross</td>\n",
       "      <td>LRT</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8326</th>\n",
       "      <td>Tollcross</td>\n",
       "      <td>LRT</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8327</th>\n",
       "      <td>Tollcross</td>\n",
       "      <td>LRT</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8328</th>\n",
       "      <td>Tollcross</td>\n",
       "      <td>LRT</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9148</th>\n",
       "      <td>Leith Walk</td>\n",
       "      <td>LRT</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9640</th>\n",
       "      <td>Silverknowes</td>\n",
       "      <td>LRT</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9641</th>\n",
       "      <td>Silverknowes</td>\n",
       "      <td>LRT</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10607</th>\n",
       "      <td>Muirhouse</td>\n",
       "      <td>LRT</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11223</th>\n",
       "      <td>Hunters Tryst</td>\n",
       "      <td>LRT</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11671</th>\n",
       "      <td>Torphin</td>\n",
       "      <td>LRT</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  name_y company num\n",
       "361          London Road     LRT   4\n",
       "362          London Road     LRT  45\n",
       "1395      Hanover Street     LRT  47\n",
       "1396      Hanover Street     LRT  45\n",
       "1397      Hanover Street     LRT  27\n",
       "1812          Canonmills     LRT  47\n",
       "1813          Canonmills     LRT  27\n",
       "2074          Crewe Toll     LRT  27\n",
       "3641               Leith     LRT  10\n",
       "5130      Princes Street     LRT   4\n",
       "5131      Princes Street     LRT  10\n",
       "5517            Newhaven     LRT  10\n",
       "6358           Haymarket     LRT   4\n",
       "6835           Brunstane     LRT  45\n",
       "7331          Northfield     LRT   4\n",
       "7332          Northfield     LRT  45\n",
       "7424             Balerno     LRT  47\n",
       "7462      Balerno Church     LRT  47\n",
       "7515   Cockburn Crescent     LRT  47\n",
       "7585              Currie     LRT  47\n",
       "7586              Currie     LRT  45\n",
       "7850             Bingham     LRT   4\n",
       "7877       Craiglockhart     LRT  47\n",
       "7878       Craiglockhart     LRT   4\n",
       "7879       Craiglockhart     LRT  45\n",
       "7880       Craiglockhart     LRT  27\n",
       "7881       Craiglockhart     LRT  10\n",
       "7936        Fairmilehead     LRT   4\n",
       "7995             Hillend     LRT   4\n",
       "8030             Oxgangs     LRT   4\n",
       "8031             Oxgangs     LRT  27\n",
       "8140            Colinton     LRT  47\n",
       "8141            Colinton     LRT  45\n",
       "8142            Colinton     LRT  10\n",
       "8222         Duddingston     LRT  45\n",
       "8257    Riccarton Campus     LRT  45\n",
       "8325           Tollcross     LRT  47\n",
       "8326           Tollcross     LRT  45\n",
       "8327           Tollcross     LRT  27\n",
       "8328           Tollcross     LRT  10\n",
       "9148          Leith Walk     LRT  10\n",
       "9640        Silverknowes     LRT  27\n",
       "9641        Silverknowes     LRT  10\n",
       "10607          Muirhouse     LRT  10\n",
       "11223      Hunters Tryst     LRT  27\n",
       "11671            Torphin     LRT  10"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(route.merge(route, how='inner', \n",
    "            left_on=['company', 'num'], right_on=['company', 'num'])\n",
    "    .merge(stops, left_on='stop_x', right_on='id')\n",
    "    .merge(stops, left_on='stop_y', right_on='id')\n",
    "    .query('name_x==\"Craiglockhart\" & company==\"LRT\"')\n",
    "    .loc[:, ['name_y', 'company', 'num']]\n",
    "    .drop_duplicates())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.\n",
    "Find the routes involving two buses that can go from **Craiglockhart** to **Lochend**.\n",
    "Show the bus no. and company for the first bus, the name of the stop for the transfer,\n",
    "and the bus no. and company for the second bus.\n",
    "\n",
    "> _Hint_    \n",
    "> Self-join twice to find buses that visit Craiglockhart and Lochend, then join those on matching stops."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>num_x</th>\n",
       "      <th>company_x</th>\n",
       "      <th>name_y</th>\n",
       "      <th>num_y</th>\n",
       "      <th>company_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>87</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>34</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>35</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>87A</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>42</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>46A</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>4</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>20</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>4</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>C5</td>\n",
       "      <td>SMT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>4</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>65</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>45</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>87</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>45</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>34</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>45</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>35</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>45</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>87A</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>45</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>42</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>45</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>46A</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>45</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>20</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>45</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>C5</td>\n",
       "      <td>SMT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>45</td>\n",
       "      <td>LRT</td>\n",
       "      <td>London Road</td>\n",
       "      <td>65</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>47</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Canonmills</td>\n",
       "      <td>34</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>47</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Canonmills</td>\n",
       "      <td>35</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>27</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Canonmills</td>\n",
       "      <td>34</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>27</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Canonmills</td>\n",
       "      <td>35</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>27</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Crewe Toll</td>\n",
       "      <td>20</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>10</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Leith</td>\n",
       "      <td>87</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>10</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Leith</td>\n",
       "      <td>34</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>10</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Leith</td>\n",
       "      <td>35</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>10</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Leith</td>\n",
       "      <td>C5</td>\n",
       "      <td>SMT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>4</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Princes Street</td>\n",
       "      <td>C5</td>\n",
       "      <td>SMT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>4</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Princes Street</td>\n",
       "      <td>65</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>10</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Princes Street</td>\n",
       "      <td>C5</td>\n",
       "      <td>SMT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>10</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Princes Street</td>\n",
       "      <td>65</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>4</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Haymarket</td>\n",
       "      <td>C5</td>\n",
       "      <td>SMT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>4</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Haymarket</td>\n",
       "      <td>65</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>45</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Duddingston</td>\n",
       "      <td>42</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>45</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Duddingston</td>\n",
       "      <td>46A</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>45</td>\n",
       "      <td>LRT</td>\n",
       "      <td>Riccarton Campus</td>\n",
       "      <td>65</td>\n",
       "      <td>LRT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   num_x company_x            name_y num_y company_y\n",
       "0      4       LRT       London Road    87       LRT\n",
       "1      4       LRT       London Road    34       LRT\n",
       "2      4       LRT       London Road    35       LRT\n",
       "3      4       LRT       London Road   87A       LRT\n",
       "4      4       LRT       London Road    42       LRT\n",
       "5      4       LRT       London Road   46A       LRT\n",
       "6      4       LRT       London Road    20       LRT\n",
       "7      4       LRT       London Road    C5       SMT\n",
       "8      4       LRT       London Road    65       LRT\n",
       "9     45       LRT       London Road    87       LRT\n",
       "10    45       LRT       London Road    34       LRT\n",
       "11    45       LRT       London Road    35       LRT\n",
       "12    45       LRT       London Road   87A       LRT\n",
       "13    45       LRT       London Road    42       LRT\n",
       "14    45       LRT       London Road   46A       LRT\n",
       "15    45       LRT       London Road    20       LRT\n",
       "16    45       LRT       London Road    C5       SMT\n",
       "17    45       LRT       London Road    65       LRT\n",
       "18    47       LRT        Canonmills    34       LRT\n",
       "19    47       LRT        Canonmills    35       LRT\n",
       "20    27       LRT        Canonmills    34       LRT\n",
       "21    27       LRT        Canonmills    35       LRT\n",
       "22    27       LRT        Crewe Toll    20       LRT\n",
       "23    10       LRT             Leith    87       LRT\n",
       "24    10       LRT             Leith    34       LRT\n",
       "25    10       LRT             Leith    35       LRT\n",
       "26    10       LRT             Leith    C5       SMT\n",
       "27     4       LRT    Princes Street    C5       SMT\n",
       "28     4       LRT    Princes Street    65       LRT\n",
       "29    10       LRT    Princes Street    C5       SMT\n",
       "30    10       LRT    Princes Street    65       LRT\n",
       "31     4       LRT         Haymarket    C5       SMT\n",
       "32     4       LRT         Haymarket    65       LRT\n",
       "33    45       LRT       Duddingston    42       LRT\n",
       "34    45       LRT       Duddingston   46A       LRT\n",
       "35    45       LRT  Riccarton Campus    65       LRT"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bus1 = (route.merge(route, how='inner', \n",
    "            left_on=['company', 'num'], right_on=['company', 'num'])\n",
    "    .merge(stops, left_on='stop_x', right_on='id')\n",
    "    .merge(stops, left_on='stop_y', right_on='id')\n",
    "    .query('name_x==\"Craiglockhart\"')\n",
    "    .loc[:, ['name_y', 'company', 'num', 'stop_y']]\n",
    "    .drop_duplicates())\n",
    "bus2 = (route.merge(route, how='inner', \n",
    "            left_on=['company', 'num'], right_on=['company', 'num'])\n",
    "    .merge(stops, left_on='stop_x', right_on='id')\n",
    "    .merge(stops, left_on='stop_y', right_on='id')\n",
    "    .query('name_y==\"Lochend\"')\n",
    "    .loc[:, ['stop_x', 'company', 'num']]\n",
    "    .drop_duplicates())\n",
    "(bus1.merge(bus2, left_on='stop_y', right_on='stop_x')\n",
    "    .loc[:, ['num_x', 'company_x', 'name_y', 'num_y', 'company_y']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
